﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	更新ERP_BH，向LCM推送数据用。表中的ERP_BH字段
-- =============================================
CREATE PROCEDURE [dbo].[proc_Department_UpdateBMBH]

AS
BEGIN
	
UPDATE Department SET ERP_BH = '' 	
	
declare @TempSca1 table(zid INT IDENTITY,did INT,bmbh VARCHAR(50) )
INSERT INTO @TempSca1(did,bmbh) SELECT Company.id,zzjg.bh
                                  FROM Company,zzjg WHERE Company.Id=zzjg.CompanyId ORDER BY Company.id
                                  
declare @TempSca2 table(did INT,bmbh VARCHAR(50) )

DECLARE @i1 INT,@maxid1 INT,@bmbh1 VARCHAR(50)
SET @i1=1
SET @maxid1=(SELECT COUNT(1) FROM @TempSca1)
--WHILE @i1<=@maxid1
WHILE @i1<=@maxid1
BEGIN

;WITH List As(SELECT [Id]
      ,[DName]
      ,[ParentId]
	,0 As jb
,cast(t.bmbh+(CASE when len(ROW_NUMBER() over(order by sort))<2 THEN '0' ELSE '' END ) + Convert(varchar(2),ROW_NUMBER() over(order by sort)) As varchar(30)) as row
,sort
FROM [Department] d,@TempSca1 AS t
Where CompanyId=t.did And [ParentId]=0 AND t.zid=@i1
Union All
Select a.id,a.DName,a.ParentId,b.jb+1
,cast(b.row + (CASE when len(ROW_NUMBER() over(order by a.ParentId,a.sort,a.id))<2 THEN '0' ELSE '' END ) + Convert(varchar(2),ROW_NUMBER() over(order by a.ParentId,a.sort,a.id)) As varchar(30)) as row
,a.sort
From [Department] a,List b
Where a.[ParentId]=b.id
)

--Select * From List Order By row



UPDATE Department SET ERP_BH =ts.row FROM List ts WHERE Department.Id=ts.id	

SET @i1=@i1+1
END
	
--declare @TempSca1 table(zid INT IDENTITY,did INT,bmbh VARCHAR(50) )
--INSERT INTO @TempSca1(did,bmbh) SELECT Company.id,zzjg.bh
--                                  FROM Company,zzjg WHERE Company.Id=zzjg.CompanyId ORDER BY Company.id


--DECLARE @i1 INT,@maxid1 INT,@bmbh1 VARCHAR(50)
--SET @i1=1
--SET @maxid1=(SELECT COUNT(1) FROM @TempSca1)
--WHILE @i1<=@maxid1
--BEGIN	
--SET @bmbh1=(SELECT bmbh FROM @TempSca1 ts WHERE ts.zid=@i1)

--DECLARE @bh1 INT ,@fbh1 VARCHAR(50),@bbh1 VARCHAR(50),@k1 INT ,@kmax1 int

--SET @k1=1
--SET @kmax1=(SELECT COUNT(1) FROM Department WHERE companyid=(SELECT did FROM @TempSca1 where zid=@i1) AND parentid=0)

--	WHILE @k1<=@kmax1
--		BEGIN		

--			set @fbh1 = (SELECT TOP 1 Department.ERP_BH FROM  Department 
--			                   WHERE Department.parentid=0 AND CompanyId=(SELECT did FROM @TempSca1 where zid=@i1) AND ERP_BH<>'' ORDER BY Sort desc, id desc)

--			SET @bh1=RIGHT(@fbh1,2)
	
--			IF (@bh1>=9)
--			BEGIN
--				set @bbh1=CONVERT(VARCHAR, @bh1+1)
--			END
--			ELSE
--				BEGIN
--				 IF @bh1 IS NULL
--				 BEGIN
--				 	set @bbh1='01'
--				 END 
--				 ELSE
--				 	BEGIN
--				 		set @bbh1='0'+CONVERT(VARCHAR, @bh1+1)
--				 	END				
--				END

	
--			UPDATE [Department] SET ERP_BH = @bmbh1+@bbh1  WHERE 
--			                   [Department].id=(SELECT top 1 id FROM Department d WHERE d.parentid=0 AND d.CompanyId=(SELECT did FROM @TempSca1 where zid=@i1)  AND d.ERP_BH='' ORDER BY Sort,id) 
--			                                         AND [Department].ERP_BH='' 
--			SET @k1=@k1+1

--		END

--------------------------------------------------------------

--declare @TempSca table(zid INT IDENTITY,did INT,bmbh VARCHAR(50) )
--INSERT INTO @TempSca(	did,bmbh) SELECT id,ERP_BH FROM Department WHERE CompanyId=(SELECT did FROM @TempSca1 AS ts1 WHERE ts1.zid=@i1) 
--     ORDER BY parentid,Sort ,id

--DECLARE @i INT,@maxid INT,@bmbh VARCHAR(50)
--SET @i=1
--SET @maxid=(SELECT COUNT(1) FROM @TempSca)
--WHILE @i<=@maxid
--BEGIN	
--SET @bmbh=(SELECT bmbh FROM @TempSca ts WHERE ts.zid=@i)
--IF @bmbh =''
--BEGIN
--	SET @bmbh=(SELECT ERP_BH FROM Department d WHERE d.id =(SELECT did FROM @TempSca ts WHERE ts.zid=@i))
--END

--DECLARE @bh INT ,@fbh VARCHAR(50),@bbh VARCHAR(50),@k INT ,@kmax int

--SET @k=1
--SET @kmax=(SELECT COUNT(1) FROM Department WHERE parentid=(SELECT did FROM @TempSca where zid=@i))

--	WHILE @k<=@kmax
--		BEGIN	
--			set @fbh = (SELECT TOP 1 Department.ERP_BH FROM  Department 
--			                   WHERE Department.parentid=(SELECT did FROM @TempSca where zid=@i) AND ERP_BH<>'' ORDER BY Sort desc,id desc)

--			SET @bh=RIGHT(@fbh,2)
--			IF (@bh>=9)
--			BEGIN
--				set @bbh=CONVERT(VARCHAR, @bh+1)
--			END
--			ELSE
--				BEGIN
--				 IF @bh IS NULL
--				 BEGIN
--				 	set @bbh='01'
--				 END 
--				 ELSE
--				 	BEGIN
--				 		set @bbh='0'+CONVERT(VARCHAR, @bh+1)
--				 	END				
--				END
--			UPDATE [Department] SET ERP_BH = @bmbh+@bbh  WHERE 
--			                   [Department].id=(SELECT top 1 id FROM Department d WHERE d.parentid=(SELECT did FROM @TempSca where zid=@i)  AND d.ERP_BH='' ORDER BY Sort,id) 
--			                                         AND [Department].ERP_BH='' 
--			SET @k=@k+1
--		END
--SET @i=@i+1
--END
--SET @i1=@i1+1
--END
END
